---
sidebar_label: Use an existing database
sidebar_position: 11
description: Set up a Hasura GraphQL schema with an existing database
keywords:
  - hasura
  - docs
  - schema
  - existing database
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

# Set Up a GraphQL Schema Using an Existing Postgres Database

## Introduction

When you have an existing database with a schema already present, you don't need to create tables or views or run DDL
queries through the Hasura Console.

All you need to do is indicate to Hasura GraphQL Engine which tables and views you want to expose over GraphQL and how
they are connected to each other so that you can query them as a "graph".

## Step 1: Track tables/views

Tracking a table or a view means telling Hasura GraphQL Engine that you want to expose that table/view over GraphQL.

### To track a table or a view

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

1.  Head to the `Data -> Schema` section of the Console.
2.  Under the heading `Untracked Tables/Views`, click on the `Track` button next to the table/view name.

</TabItem>
<TabItem value="cli" label="CLI">

To track the table and expose it over the GraphQL API, add it to the `tables.yaml` file in the `metadata` directory as
follows:

```yaml {1-3}
- table:
    schema: public
    name: <table name>
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

To track a table and expose it over the GraphQL API, use the
[pg_track_table](/api-reference/metadata-api/table-view.mdx#metadata-pg-track-table) Metadata API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
   "type": "pg_track_table",
   "args": {
      "source": "<db_name>",
      "schema": "public",
      "name": "<table_name>"
   }
}
```

</TabItem>
</Tabs>

### To track all tables and views present in the database

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

1.  Head to the `Data -> Schema` section of the Console.
2.  Under the heading `Untracked Tables/Views`, click the `Track All` button.

</TabItem>
<TabItem value="cli" label="CLI">

To track all tables and expose them over the GraphQL API, add them to the `tables.yaml` file in the `metadata` directory
as follows:

```yaml {1-6}
- table:
    schema: public
    name: <table-name-1>
- table:
    schema: public
    name: <table-name-2>
```

To automate this, you could add the tables in a loop through a script.

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

To track all tables and expose them over the GraphQL API, use the
[pg_track_table](/api-reference/metadata-api/table-view.mdx#metadata-pg-track-table) Metadata API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "bulk",
  "args": [
    {
       "type": "pg_track_table",
       "args": {
          "source": "<db_name>",
          "schema": "public",
          "name": "<table-name-1>"
       }
    },
    {
       "type": "pg_track_table",
       "args": {
          "source": "<db_name>",
          "schema": "public",
          "name": "<table-name-2>"
       }
    }
  ]
}
```

To automate this, you could add the `pg_track_table` requests to the `bulk` request in a loop through a script.

</TabItem>
</Tabs>

## Step 2: Track foreign-keys

Tracking a foreign-key means creating a [relationship](/schema/postgres/table-relationships/index.mdx) between the
tables involved in the foreign-key.

### To track a foreign-key between two tables in the database

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

1.  Head to the `Data -> Schema` section of the Console.
2.  Click on a table involved in the foreign-key and head to the `Relationships` tab.
3.  You should see a suggested relationship based on the foreign-key. Click `Add`, give a name to your relationship
    (this will be the name of the [nested object](/queries/postgres/nested-object-queries.mdx) in the GraphQL query),
    and hit `Save` to create the relationship.
4.  Repeat with the other table involved in the foreign-key.

</TabItem>
<TabItem value="cli" label="CLI">

To track a relationship and expose it over the GraphQL API, add it to the `tables.yaml` file in the `metadata` directory
as follows:

**Object relationship**

```yaml {4-7}
- table:
    schema: public
    name: <table name>
  object_relationships:
    - name: <relationship name>
      using:
        foreign_key_constraint_on: <reference column>
```

**Array relationship**

```yaml {4-11}
- table:
      schema: public
      name: <table name>
   array_relationships:
   - name: <relationship name>
      using:
      foreign_key_constraint_on:
         column: <reference column>
         table:
            schema: public
            name: <reference table name>
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

**Object relationship**

You can create an object relationship by using the
[pg_create_object_relationship](/api-reference/metadata-api/relationship.mdx#metadata-pg-create-object-relationship)
Metadata API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
   "type": "pg_create_object_relationship",
   "args": {
      "source": "<db_name>",
      "table": "<table name>",
      "name": "<relationship name>",
      "using": {
         "foreign_key_constraint_on": "<reference column>"
      }
   }
}
```

**Array relationship**

You can create an array relationship by using the
[pg_create_array_relationship](/api-reference/metadata-api/relationship.mdx#metadata-pg-create-array-relationship)
Metadata API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
   "type": "pg_create_array_relationship",
   "args": {
      "source": "<db_name>",
      "table": "<table name>",
      "name": "<relationship name>",
      "using": {
         "foreign_key_constraint_on" : {
            "table" : "<reference table name>",
            "column" : "<reference column>"
         }
      }
   }
}
```

</TabItem>
</Tabs>

### To track all the foreign-keys of all tables in the database

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

1.  Head to the `Data -> Schema` section of the Console.
2.  Under the heading `Untracked foreign-key relations`, click the `Track All` button to automatically create
    relationships based on the foreign-keys.

</TabItem>
<TabItem value="cli" label="CLI">

To track all relationships and expose them over the GraphQL API, add them to the `tables.yaml` file in the `metadata`
directory as follows:

**Object relationship**

```yaml {4-7}
- table:
    schema: public
    name: <table name>
  object_relationships:
    - name: <relationship name>
      using:
        foreign_key_constraint_on: <reference column>
```

**Array relationship**

```yaml {4-11}
- table:
    schema: public
    name: <table name>
  array_relationships:
    - name: <relationship name>
      using:
        foreign_key_constraint_on:
          column: <reference column>
          table:
            schema: public
            name: <reference table name>
```

To automate this, you could add the relationships in a loop through a script.

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

You can create multiple relationships by using the
[pg_create_object_relationship](/api-reference/metadata-api/relationship.mdx#metadata-pg-create-object-relationship) and
the [pg_create_array_relationship](/api-reference/metadata-api/relationship.mdx#metadata-pg-create-array-relationship)
Metadata APIs:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "bulk",
  "args": [
    {
      "type": "pg_create_object_relationship",
      "args": {
        "source": "<db_name>",
        "table": "<table name>",
        "name": "<relationship name>",
        "using": {
          "foreign_key_constraint_on": "<reference column>"
        }
      }
    },
    {
      "type": "pg_create_array_relationship",
      "args": {
        "source": "<db_name>",
        "table": "<table name>",
        "name": "<relationship name>",
        "using": {
          "foreign_key_constraint_on" : {
            "table" : "<reference table name>",
            "column" : "<reference column>"
          }
        }
      }
    }
  ]
}
```

To automate this, you could add the create relationships requests to the `bulk` request in a loop through a script.

</TabItem>
</Tabs>

:::info Relationship nomenclature

In this case, Hasura GraphQL Engine will **automatically generate relationship names** (the names of the
[nested objects](/queries/postgres/nested-object-queries.mdx) in the GraphQL query) based on the table names and the
foreign-key names.

The name is generated in the following format:

- For object relationships: `singular of foreignTableName`
- For array relationships: `plural of foreignTableName`

For example, for the foreign-key `article.author_id -> author.id`, the relationship names will be `author` for `article`
table and `articles` for `author` table.

In case a field with the generated name already exists, a new name will be generated of the form:
`camel case of (singular/plural of foreignTableName + _by_ + foreignKeyColumnName)`

Note that, **this is just an arbitrary naming convention** chosen by Hasura to ensure the generation of unique
relationship names. You can choose to rename your relationships to anything you wish. You can **change the relationship
names** with a name of your choice as shown in
[renaming relationships](/schema/postgres/table-relationships/rename.mdx).

:::
